﻿CREATE PROCEDURE admin.s_Snapshot AS
SET NOCOUNT ON;

-- Change to the master database

DECLARE @DbName sysname;
SET @DbName = N'Vantage_In';

PRINT 'Drop existing snaphost';
PRINT '';
-- Drop the snapshot of the AdventureWorks database, if it exists
IF  EXISTS (SELECT [name] FROM [master].[dbo].[sysdatabases] WHERE [name] = @DBName+N'_Snapshot')
DROP DATABASE [AdventureWorksSnapshot];

PRINT 'Create the snaphost';
PRINT '';

-- Create a snapshot of the AdventureWorks database
DECLARE @Sql nvarchar(max);
DECLARE @Pos int;

PRINT (
N'DECLARE @Sql NVARCHAR(MAX); 

SET @Sql = N''CREATE DATABASE [' + @DbName + N'_Snapshot] ON ''; 

SELECT @Sql = @Sql + CHAR(13) + N''(NAME=['' + [name] + N''],'' 
    + CHAR(13) + N''FILENAME=N'''''' +  
    LEFT([physical_name], 
        CHARINDEX(N''.'', 
            [physical_name], 
            LEN([physical_name]) - 5) - 1) 
    + N''Snapshot'' 
    + SUBSTRING([physical_name], 
        CHARINDEX(N''.'', 
            [physical_name], 
            LEN([physical_name]) - 5), 5) + N''''''),'' 
FROM [master].[sys].[master_files] 
WHERE [type] = 0 
    AND [database_id] = DB_ID(N''' + @DbName + N''');

SET @Sql = LEFT(@Sql, LEN(@Sql) - 1) + N'' AS SNAPSHOT OF [' + @DbName + N'];''

PRINT @Sql;
PRINT '''';

EXECUTE (@Sql);'
); 

PRINT 'Display the snapshot databases'
PRINT '';

-- Display the snapshot databases
SELECT N'[' + db1.[name] + N'] is a snapshot of database ['+ db2.[name] + N']' AS 'Snapshot Databases'
FROM [master].[sys].[databases] db1 
    INNER JOIN [master].[sys].[databases] db2 
    ON db1.[source_database_id] = db2.[database_id];

